package com.elite.groovy.dev;

import com.elite.groovy.kh.KHExcelUtil;
import com.elite.groovy.kh.TxConfig;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @projectName: ngsapitask-davco
 * @package: com.elite.groovy.dev
 * @className: ReadExcel
 * @author: LiJT
 * @description: TODO
 * @date: 2024-03-14 16:46
 * @version: 1.0
 */
public class ReadExcel {
    private static final Log log = LogFactory.getLog(ReadExcel.class);
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");

    public static void main(String[] args) throws Exception {

        String IMPORT_BATCH_GUID = sdf.format(new Date());
        // 表头
        // String file_Path = "C:\\Users\\LiJT\\Desktop\\111.xlsx";
        String file_Path = "C:\\Users\\LiJT\\Desktop\\业务员导入模板2024-06-06.xlsx";

        List<List> file_Datas = KHExcelUtil.readExcel(file_Path, 1, 0);

        long startTime = System.currentTimeMillis();
        JdbcTemplate jt = new JdbcTemplate(TxConfig.getDruidDataSource());
        int Db_Batch_Num = 2000;
        String table_SQL = "Insert into DG_IMPORT_BUSINESS_TEMP(REC_GUID,BUSINESS_CATEGORY,REGION,PROVINCE,CITY,DISTRICT,SALES_ORGANIZATION,BUSINESS_STAFF,BUSINESS_MOBILE,DEAL_FLAG,IMPORT_BATCH_GUID) Values (uuid(),?,?,?,?,?,?,?,?,0,'" + IMPORT_BATCH_GUID + "')";
        int params_Count = 8;
        log.debug("当前doSyncInTable #Table_SQL:" + table_SQL + " 参数(数量):" + params_Count);

        List<List<String>> table_Datas = file_Datas.get(0);

        int totolNum = 0;
        int loopNum = 0;
        List<String[]> paramListCus = new ArrayList<>();
        for (int i = 0; i < table_Datas.size(); i++) {

            List<String> row_Data = table_Datas.get(i);
            String[] cusArr = new String[params_Count];

            if (row_Data.size() < params_Count) {
                log.warn("行中数据列缺失,数据放弃处理 行号:" + i);
                continue;
            }

            for (int j = 0; j < params_Count; j++) {
                cusArr[j] = row_Data.get(j);
            }

            paramListCus.add(cusArr);
            loopNum++;
            totolNum++;

        }

        // 4.批量写入
        long dbStartTime = System.currentTimeMillis();
        jt.batchUpdate(table_SQL, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String[] oneRow = paramListCus.get(i);
                ps.setObject(1, oneRow[0]);
                ps.setObject(2, oneRow[1]);
                ps.setObject(3, oneRow[2]);
                ps.setObject(4, oneRow[3]);
                ps.setObject(5, oneRow[4]);
                ps.setObject(6, oneRow[5]);
                ps.setObject(7, oneRow[6]);
                ps.setObject(8, oneRow[7]);
            }

            @Override
            public int getBatchSize() {
                // 必须有值,一般是需要写入的数量
                return paramListCus.size();
            }
        });

        log.debug("1处理完成 耗时(毫秒):" + (System.currentTimeMillis() - dbStartTime) + " 总数量:" + totolNum);

        JdbcTemplate sd= TxConfig.getJdbcTemplate();
        dbStartTime = System.currentTimeMillis();
        sd.batchUpdate(table_SQL,new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return paramListCus.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                String[] oneRow = paramListCus.get(i);
                ps.setObject(1, oneRow[0]);
                ps.setObject(2, oneRow[1]);
                ps.setObject(3, oneRow[2]);
                ps.setObject(4, oneRow[3]);
                ps.setObject(5, oneRow[4]);
                ps.setObject(6, oneRow[5]);
                ps.setObject(7, oneRow[6]);
                ps.setObject(8, oneRow[7]);
            }
        });
        log.debug("2处理完成 耗时(毫秒):" + (System.currentTimeMillis() - dbStartTime) + " 总数量:" + totolNum);

        long time = countOccurrences(table_SQL,paramListCus);

        log.debug("3处理完成 耗时(毫秒):" + time + " 总数量:" + totolNum);

    }

    public static long countOccurrences(String insertSQL, List<String[]> paramListCus) {
        String url = "jdbc:mysql://127.0.0.1:3306/ques_data?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "HSDev";
        String password = "letmein";

        long startTime = System.currentTimeMillis();
        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {

            conn.setAutoCommit(false); // 禁用自动提交

            // 批量插入100条数据
            for (int i = 0; i < paramListCus.size(); i++) {
                String[] oneRow = paramListCus.get(i);
                pstmt.setObject(1, oneRow[0]);
                pstmt.setObject(2, oneRow[1]);
                pstmt.setObject(3, oneRow[2]);
                pstmt.setObject(4, oneRow[3]);
                pstmt.setObject(5, oneRow[4]);
                pstmt.setObject(6, oneRow[5]);
                pstmt.setObject(7, oneRow[6]);
                pstmt.setObject(8, oneRow[7]);
                pstmt.addBatch(); // 添加到批处理
            }

            // 执行批量操作
            pstmt.executeBatch();

            // 提交事务
            conn.commit();

            System.out.println("Batch insertion completed successfully 耗时(毫秒):" + (System.currentTimeMillis() - startTime));

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return  (System.currentTimeMillis() - startTime);
    }

}
